package com.example.mapper;

import com.example.info.DeviceInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface DeviceDataMapper {

    //从machineStatusHis获取不同machineID的最新数据
    @Select("SELECT * FROM (" +
            "SELECT *, ROW_NUMBER() OVER (PARTITION BY machineID ORDER BY postTime DESC) AS rn " +
            "FROM machineStatusHis" +
            ") AS temp WHERE rn = 1")
    public List<DeviceInfo> getData();

    //新增信息
    @Insert("INSERT INTO machineStatusHis (machineID, postTime, shiftName, numTotal, numRemove, jianchelunJuanyanHeidian, jianchelunJuanyanBaidian, jianchelunJuanyanXiachi, jianchelunShuisongHeidian, jianchelunShuisongBaidian, jianchelunShuisongXiachi, jianchelunCuoya, jianchelunDingwei, tifeilunJuanyanHeidian, tifeilunJuanyanBaidian, tifeilunJuanyanXiachi, tifeilunShuisongHeidian, tifeilunShuisongBaidian, tifeilunShuisongXiachi, tifeilunCuoya, tifeilunDingwei, yanzhiChangdu, shuisongzhiChangdu, camera0, camera1, camera2, camera3, machineSpeed, errNo, removeStatus) " +
            "VALUES (#{machineID}, #{postTime}, #{shiftName}, #{numTotal}, #{numRemove}, #{jianchelunJuanyanHeidian}, #{jianchelunJuanyanBaidian}, #{jianchelunJuanyanXiachi}, #{jianchelunShuisongHeidian}, #{jianchelunShuisongBaidian}, #{jianchelunShuisongXiachi}, #{jianchelunCuoya}, #{jianchelunDingwei}, #{tifeilunJuanyanHeidian},#{tifeilunJuanyanBaidian}, #{tifeilunJuanyanXiachi}, #{tifeilunShuisongHeidian}, #{tifeilunShuisongBaidian}, #{tifeilunShuisongXiachi}, #{tifeilunCuoya}, #{tifeilunDingwei}, #{yanzhiChangdu}, #{shuisongzhiChangdu}, #{camera0}, #{camera1}, #{camera2}, #{camera3}, #{machineSpeed}, #{errNo}, #{removeStatus})")
    public void save(DeviceInfo deviceInfo);


    //获取所有设备信息
    @Select("SELECT * FROM machineStatusHis")
    public List<DeviceInfo> getAll();
    //倒序获取所有设备数据
    @Select("SELECT * FROM machineStatusHis ORDER BY statusID DESC")
    public List<DeviceInfo> getAllDesc();

    //根据machineID从machine表中获取machinName
    @Select("SELECT machineName FROM machine WHERE machineID = #{machineID}")
    public String getMachineName(String machineID);

    //根据machineID从machine表中获取IPAddress
    @Select("SELECT IPAddress FROM machine WHERE machineID = #{machineID}")
    public String getIPAddress(String machineID);

    //根据machineID从machine表中获取isConnected
    @Select("SELECT isConnected FROM machine WHERE machineID = #{machineID}")
    public Integer getIsConnected(String machineID);

    //获取设备数量
    @Select("SELECT COUNT(*) FROM machineStatusHis")
    int getCount();

    //删除数据库里的第一条数据
    @Delete("DELETE FROM machineStatusHis\n" +
            "ORDER BY statusID ASC\n" +
            "LIMIT 1;")
    public void deleteOldData();


}
